package com.projectframe.base.report;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.projectframe.base.exception.BaseException;

/**
 * 產生Excel報表檔, 使用Apache POI<br/>
 *
 * @author
 *
 */
public class PoiExcelReport 
{
	private static final Logger LOG = Logger.getLogger(PoiExcelReport.class);
	
	private String templateFile;
	private String outputFile;
	private List<Object[]>[] reportData;
	private Workbook wb;
	private CellStyle defaultCS = null;
	
	public List<Object[]>[] getReportData() 
	{
		return reportData;
	}
	public void setReportData(final List<Object[]>[] reportData) 
	{
		this.reportData = reportData;
	}
	public String getTemplateFile() 
	{
		return templateFile;
	}
	public void setTemplateFile(final String templateFile) 
	{
		this.templateFile = templateFile;
	}
	public String getOutputFile() 
	{
		return outputFile;
	}
	public void setOutputFile(final String outputFile) 
	{
		this.outputFile = outputFile;
	}
	
	/**
	 * 生成PoiExcelReport物件
	 * @param templateFile 設定Excel模版檔案
	 * @param outputFile 設定Excel報表產生檔案
	 * @param reportData 設定報表資料
	 */
	public PoiExcelReport(final String templateFile, final String outputFile, final List<Object[]>[] reportData) 
	{
		this.templateFile = templateFile;
		this.outputFile = outputFile;
		this.reportData = reportData;
	}
	
	public void export(final int[] startDataRow) 
	{
		export(startDataRow, null);
	}
	
	/**
	 * 依PoiExcelReport constructor的設定, 產生excel檔案.
	 * @param startDataRow 設定每一個sheet資料開始寫入的row number, excel的第一個row為0.
	 */
	public void export(final int[] startDataRow, final Map<?,?>[] headMap) 
	{
		InputStream inp = null;
		
		try 
		{
			inp = new FileInputStream(this.templateFile);
		} 
		catch (FileNotFoundException e) 
		{
			LOG.error("Open excel report template file error, " + e.getMessage());
			final BaseException pos3ge = new BaseException("S411001", e);
			throw pos3ge;
		}
		
		FileOutputStream out = null;
		
		try 
		{
			wb = WorkbookFactory.create(inp);
			createDefaultCS();
			
			// get sheet
			if (null != this.reportData && this.reportData.length > 0) 
			{
				final int sheetSize = this.reportData.length;
				for (int sheetNum = 0; sheetNum < sheetSize; sheetNum++) 
				{
					final Sheet sheet = wb.getSheetAt(sheetNum);
					if (null != headMap) 
					{						
						if(this.reportData[sheetNum]!=null)
							processSheet(sheet, this.reportData[sheetNum], startDataRow[sheetNum], headMap[sheetNum]);
						else
							sheetNum++;
					} 
					else 
					{
						if(this.reportData[sheetNum]!=null)
						{
							processSheet(sheet, this.reportData[sheetNum], startDataRow[sheetNum], null);
						}
						else
						{
							sheetNum++;
						}
					}
				}
				
				// save the excel file
				out = new FileOutputStream(this.outputFile);
				wb.write(out);
			}
		} 
		catch (Exception e) 
		{
			LOG.error("Create excel report file error, " + e.getMessage());
			final BaseException pos3ge = new BaseException("S411001", e);
			throw pos3ge;
		} 
		finally 
		{
			if (null != inp) 
			{
				try 
				{
					inp.close();
				} 
				catch (Exception e) 
				{
					e.printStackTrace();
				}
			}
			if (null != out) 
			{
				try 
				{
					out.close();
				} 
				catch (Exception e) 
				{
					e.printStackTrace();
				}
			}
		}
	}
	
	private void processSheet(final Sheet sheet, final List<Object[]> reportPageData, 
			                  final int startDataRow, final Map<?,?> headMap) 
	{
		if (null != headMap)
			processHeader(sheet, startDataRow, headMap);
		
		if (null != reportPageData && !reportPageData.isEmpty())
		{
			try
			{
				final CellStyle[] cs = createCellStyle(sheet, reportPageData, startDataRow);
				if(cs!=null)
					writeCellData(sheet, reportPageData, startDataRow, cs);
			}
			catch(NullPointerException npe)
			{
				LOG.info("Currnt sheet has no data to export.");
			}
		}
	}
	
	private void processHeader(final Sheet sheet, final int startDataRow, final Map<?,?> headMap) 
	{
		final int rowIdx = startDataRow - 1;
		
		for (int i = 0; i < rowIdx; i++) 
		{
			final Row headRow = sheet.getRow(i);
			for (Cell cell : headRow) 
			{
				if (Cell.CELL_TYPE_STRING == cell.getCellType()) 
				{
					final String cellValue = cell.getStringCellValue();

					if (headMap.containsKey(cellValue))
						cell.setCellValue((String) headMap.get(cellValue));
				}
			}
		}
	}
	
	private CellStyle[] createCellStyle(final Sheet sheet, final List<Object[]> reportPageData, final int startDataRow) 
	{
		Object[] row0=null;
		int colsize=0;
		
		// get the cell style
		try
		{
			if(reportPageData.isEmpty())
			{
				LOG.debug("report data is empty");
				return null;
			}
		}
		catch(NullPointerException npe)
		{
			LOG.debug("report data is empty and caught NullPointerException!");
			return null;
		}
		
		row0 = reportPageData.get(0);
		colsize = row0.length;

		// create cell style from header row
		final CellStyle[] cs = new CellStyle[colsize];
		final int headIdx = startDataRow - 1;
		final Row headRow = sheet.getRow(headIdx);
		
		for (int i = 0; i < colsize; i++) 
		{
			final Cell headCell = headRow.getCell(i);

			cs[i] = wb.createCellStyle();
			cs[i].setBorderBottom(CellStyle.BORDER_THIN);
			cs[i].setBorderLeft(CellStyle.BORDER_THIN);
			cs[i].setBorderRight(CellStyle.BORDER_THIN);
			cs[i].setBorderTop(CellStyle.BORDER_THIN);
			
			if (null != headCell) 
			{
				final CellStyle tmpCs = headCell.getCellStyle();
				cs[i].setDataFormat(tmpCs.getDataFormat());
				cs[i].setFont(wb.getFontAt(tmpCs.getFontIndex()));
			}
		}
		
		return cs;
	}
	
	private void writeCellData(final Sheet sheet, final List<Object[]> reportPageData, 
			                   final int startDataRow, final CellStyle[] cs) 
	{
		//write data to data row
		final Object[] row0 = reportPageData.get(0);
		if(row0==null)
			return;
		
		//final int colsize = row0.length;
		int rowIdx = startDataRow;
		
		// create helper
		final CreationHelper helper = wb.getCreationHelper();
		// creat the drawing partriarch
		final Drawing drawing = sheet.createDrawingPatriarch();
		
		for (Object[] data : reportPageData) 
		{
			final Row row = sheet.createRow(rowIdx);

			for (int i = 0; i < data.length; i++) 
			{
				final Cell cell = row.createCell(i);
				if (null != data[i]) 
				{
					if (data[i] instanceof String) 
					{
						cell.setCellValue((String) data[i]);
					} 
					else if (data[i] instanceof Date) 
					{
						cell.setCellValue((Date) data[i]);
					} 
					else if (data[i] instanceof Double) 
					{
						cell.setCellValue((Double) data[i]);
					} 
					else if (data[i] instanceof BigDecimal) 
					{
						cell.setCellValue(((BigDecimal) data[i]).doubleValue());
					} 
					else if (data[i] instanceof PoiCellObject) 
					{
						if (PoiCellObject.OBJ_TYPE_PNG == ((PoiCellObject) data[i]).getType()) 
						{
							sheet.setColumnWidth(i, 7000);
							row.setHeightInPoints(50);
							// 處理PNG圖
							final ClientAnchor anchor = helper.createClientAnchor();
							anchor.setCol1(cell.getColumnIndex());
							anchor.setRow1(cell.getRowIndex());
							anchor.setCol2(cell.getColumnIndex());
							anchor.setRow2(cell.getRowIndex());
							final int pictureIdx = wb.addPicture((byte[]) ((PoiCellObject) data[i]).getObj(), Workbook.PICTURE_TYPE_PNG);
							final Picture pict = drawing.createPicture(anchor, pictureIdx);
							pict.resize(1.3);
							
						}
					} 
					else 
					{
						cell.setCellValue((String) data[i].toString());
					}
					
					if (!(data[i] instanceof PoiCellObject)) 
					{
						cell.setCellStyle(getCellStyle(cs[i]));
					}
				}
				else
				{
					cell.setCellStyle(getCellStyle(cs[i]));
				}
			}
			
			rowIdx++;
		}
	}
	
	private void createDefaultCS() 
	{
		defaultCS = wb.createCellStyle();
		defaultCS.setBorderBottom(CellStyle.BORDER_THIN);
		defaultCS.setBorderLeft(CellStyle.BORDER_THIN);
		defaultCS.setBorderRight(CellStyle.BORDER_THIN);
		defaultCS.setBorderTop(CellStyle.BORDER_THIN);
	}
	
	private CellStyle getCellStyle(final CellStyle cs) 
	{
		if (null != cs)
			return cs;
		
		return defaultCS;
	}
}
